package org.xukai.dal.util;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * 导入数据
 * Created by liumingjian on 2017/7/14.
 */
public class ImportDataMain {

    private static final String csvFile = "/consumed_event_2017_07_17.csv";

    private static final String tableName = "received_event_2017_07_17";

    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

    public static void main2(String[] args) throws IOException {
        if(StringUtils.isEmpty(csvFile)){
            throw new IllegalArgumentException("请输入CSV文件地址");
        }

        log("开始加载数据");
        String sql = RaycloudDBImportUtils.generateSqlInsert(tableName, ImportDataMain.class.getResourceAsStream(csvFile));
        log("生成数据成功，开始导入");
        SingleConnectionDataSource dataSource = createDataSource();
        log("连接数据库成功");
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        toDb(jdbcTemplate, sql);
        dataSource.destroy();
    }

    public static void main(String[] args) throws IOException, SQLException {

        SingleConnectionDataSource dataSource = createDataSource();
        Connection connection = dataSource.getConnection();
        PreparedStatement ps = connection.prepareStatement("insert into coord_jobs2222 (id,job_xml) VALUES (?,?)");
        ps.setString(1, "23");
        ps.setBlob(2, new FileInputStream(new File("")));

        dataSource.destroy();
    }

    static void log(String msg){
        System.out.println(now() + " " + msg);
    }

    static String now(){
        return format(new Date());
    }

    static String format(Date date){
        return sdf.format(date);
    }

    static void toDb(JdbcTemplate jdbcTemplate, String sql){
        int lines = jdbcTemplate.update(sql);
        log("导入成功，影响行数：" + lines);
    }

    static SingleConnectionDataSource createDataSource(){
        SingleConnectionDataSource ds = new SingleConnectionDataSource("jdbc:mysql://127.0.0.1:3306/ecadmin?relaxAutoCommit=true&useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&failOverReadOnly=false", "superboss", "guanghua", true);
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        return ds;
    }
}
